{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "\n",
    "folder = \"/media/jie/新加卷/pku_data/3571万专利申请全量数据1985-2022年\"\n",
    "file = \"/media/jie/新加卷/pku_data/3571万专利申请全量数据1985-2022年/安徽省.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "看所有专利的column是否都是一样的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# c_s = set()\n",
    "\n",
    "# for file in os.listdir(folder):\n",
    "#     if file.endswith(\".csv\"):\n",
    "#         file_name = os.path.join(folder, file)\n",
    "#         df = pd.read_csv(file_name, nrows=3)\n",
    "#         c_s.add(\n",
    "#             tuple(sorted(list(df.columns)))\n",
    "#         )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_278211/2131829638.py:1: DtypeWarning: Columns (18,19,25) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(file)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1342364, 26)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['专利公开号', '专利名称', '专利类型', '专利摘要', '申请人', '专利申请号', '申请日', '申请公布日',\n",
       "       '授权公布号', '授权公布日', '申请地址', '主权项', '发明人', '分类号', '主分类号', '代理机构', '分案原申请号',\n",
       "       '优先权', '国际申请', '国际公布', '代理人', '省份或国家代码', '法律状态', '专利领域', '专利学科',\n",
       "       '多次公布'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 966384 entries, 0 to 966383\n",
      "Data columns (total 26 columns):\n",
      " #   Column   Non-Null Count   Dtype \n",
      "---  ------   --------------   ----- \n",
      " 0   专利公开号    966384 non-null  object\n",
      " 1   专利名称     966384 non-null  object\n",
      " 2   专利类型     966384 non-null  object\n",
      " 3   专利摘要     966384 non-null  object\n",
      " 4   申请人      966384 non-null  object\n",
      " 5   专利申请号    966384 non-null  object\n",
      " 6   申请日      966384 non-null  object\n",
      " 7   申请公布日    369935 non-null  object\n",
      " 8   授权公布号    966384 non-null  object\n",
      " 9   授权公布日    596449 non-null  object\n",
      " 10  申请地址     966384 non-null  object\n",
      " 11  主权项      869144 non-null  object\n",
      " 12  发明人      966384 non-null  object\n",
      " 13  分类号      966384 non-null  object\n",
      " 14  主分类号     966384 non-null  object\n",
      " 15  代理机构     763339 non-null  object\n",
      " 16  分案原申请号   1724 non-null    object\n",
      " 17  优先权      5247 non-null    object\n",
      " 18  国际申请     324 non-null     object\n",
      " 19  国际公布     1047 non-null    object\n",
      " 20  代理人      763339 non-null  object\n",
      " 21  省份或国家代码  966384 non-null  int64 \n",
      " 22  法律状态     965777 non-null  object\n",
      " 23  专利领域     966384 non-null  object\n",
      " 24  专利学科     966384 non-null  object\n",
      " 25  多次公布     181356 non-null  object\n",
      "dtypes: int64(1), object(25)\n",
      "memory usage: 191.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 属性列最长字符串长度输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['实用新型', '外观设计', '发明公开', '发明授权'], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"专利类型\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['H05K7/20', 'F16L55/32', 'B01D29/00', ..., 'B23G1/52', 'A61F13/46',\n",
       "       'B28B21/96'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"主分类号\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.一种控制柜内部的防护装置,其特征在于,还包括控制柜(1)、合页(2)、两组第一柜门(3)、两组第二柜门(4)、制冷器(5)和过滤网(6),控制柜(1)设置有腔室,通过合页(2)使两组第一柜门(3)和两组第二柜门(4)转动安装在控制柜(1)上,并且两组第一柜门(3)和两组第二柜门(4)与控制柜(1)的腔室相通,制冷器(5)安装在控制柜(1)的腔室内,过滤网(6)安装在控制柜(1)的腔室内,通过过滤网(6)对制冷器(5)起到气体过滤作用。'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"主权项\"][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0, 10, 11, 12, 13}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(map(lambda x: len(x) if isinstance(x, str) else 0, df[\"多次公布\"].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_278211/52418007.py:4: FutureWarning: DataFrame.applymap has been deprecated. Use DataFrame.map instead.\n",
      "  max_lengths = string_columns.applymap(lambda x: len(str(x)) if pd.notnull(x) else 0).max()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "专利公开号        14\n",
      "专利名称         89\n",
      "专利类型          4\n",
      "专利摘要       3877\n",
      "申请人         250\n",
      "专利申请号        16\n",
      "申请日          10\n",
      "申请公布日        10\n",
      "授权公布号        13\n",
      "授权公布日        10\n",
      "申请地址        101\n",
      "主权项       17406\n",
      "发明人         235\n",
      "分类号         368\n",
      "主分类号         12\n",
      "代理机构         30\n",
      "分案原申请号       25\n",
      "优先权         318\n",
      "国际申请         29\n",
      "国际公布         29\n",
      "代理人           8\n",
      "法律状态       4039\n",
      "专利领域         15\n",
      "专利学科         31\n",
      "多次公布         14\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "string_columns = df.select_dtypes(include=['object'])\n",
    "\n",
    "# 计算每个字符串列中最长的字符长度\n",
    "max_lengths = string_columns.applymap(lambda x: len(str(x)) if pd.notnull(x) else 0).max()\n",
    "\n",
    "print(max_lengths)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "长度截断，如果企业的名字超过了63个字则丢掉该企业，patent_public_number要小于31个字符"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(966384, 26)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "966384"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"专利公开号\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[10000].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['专利公开号', '专利名称', '专利类型', '专利摘要', '申请人', '专利申请号', '申请日', '申请公布日',\n",
       "       '授权公布号', '授权公布日', '申请地址', '主权项', '发明人', '分类号', '主分类号', '代理机构', '分案原申请号',\n",
       "       '优先权', '国际申请', '国际公布', '代理人', '法律状态', '专利领域', '专利学科', '多次公布'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_lengths.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'省份或国家代码'}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.columns) - set(max_lengths.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([42])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"省份或国家代码\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_data2mysql(patent_public_number, company):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for _, row in tqdm(df.iterrows(), total=len(df)):\n",
    "    print(row)\n",
    "    patent_public_number = row['专利公开号']\n",
    "    companies = row[\"申请人\"]\n",
    "    if isinstance(companies, str):\n",
    "        companies = companies.replace(' ', '')\n",
    "        companies = companies.strip()\n",
    "    else:\n",
    "        continue\n",
    "    \n",
    "    if len(companies) <= 4:\n",
    "        continue\n",
    "    companies = companies.split('; ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "persons = df[df[\"申请人\"].str.split(';').apply(len) > 1][\"申请人\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"申请人\"][:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
